﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.ProviderBase;
using System.Data.Common;
using System.Data.SqlClient;

namespace Code
{
    public class MemberDataAccess
    {
        protected DataProvider _provider = DataProvider.GlobalDataProvider;

        public DataTable GetIdList()
        {
            string strSQL = string.Format("SELECT Id FROM NGUOI_DUNG");
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public bool AddMember(MemberInfo member)
        {
            string strSQL = string.Format("Insert Into NGUOI_DUNG Values({0}, '{1}', '{2}', N'{3}', N'{4}', {5}, '{6}', N'{7}', '{8}', {9}, {10}, '{11}', '{12}')",
                                            member.ID, member.MatKhau, member.Email, member.Ten, member.DiaChi, member.GioiTinh, member.NgaySinh, member.NgheNghiep,
                                            member.DienThoai, member.Diem, member.idLoaiNguoiDung, member.ThoiGianDangNhapTruoc, member.TenDangNhap);

            if (_provider.ExecuteNonQuery(strSQL) > 0)
                return true;
            else
                return false;
        }

        public DataTable GetMemberInfor(int memID)
        {
            string strSQL = string.Format("SELECT TenDangNhap, Ten, DiaChi, Email, Gioitinh, NgaySinh, NgheNghiep, DienThoai, Diem, ThoiGianDangNhapTruoc FROM [NGUOI_DUNG] WHERE ID = {0}", memID);

            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public bool CheckUsername(String strUsername)
        {
            string strSQL = string.Format("Select TenDangNhap From NGUOI_DUNG Where TenDangNhap = '{0}' COLLATE SQL_Latin1_General_CP1_CS_AS", strUsername);
            DataTable dt = _provider.ExecuteQueryDataTable(strSQL);
            if ((dt != null) && (dt.Rows.Count != 0))
            {
                return true;
            }
            else
                return false;
        }

        public bool CheckEmail(String strEmail)
        {
            string strSQL = string.Format("Select Email From NGUOI_DUNG Where Email = '{0}' COLLATE SQL_Latin1_General_CP1_CS_AS", strEmail);
            DataTable dt = _provider.ExecuteQueryDataTable(strSQL);
            if ((dt != null) && (dt.Rows.Count != 0))
            {
                return true;
            }
            else
                return false;
        }

        public DataTable GetMemberAccount(String strUsername)
        {
            string strSQL = string.Format("Select Id, TenDangNhap, MatKhau From NGUOI_DUNG Where TenDangNhap = '{0}' COLLATE SQL_Latin1_General_CP1_CS_AS", strUsername);
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetMemberAccount(int memID)
        {
            string strSQL = string.Format("Select TenDangNhap, MatKhau, TenDangNhap From NGUOI_DUNG Where Id = '{0}' COLLATE SQL_Latin1_General_CP1_CS_AS", memID);
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public String GetMemberType(String strUsername)
        {
            string strSQL = string.Format("Select TenLoaiNguoiDung From LOAI_NGUOI_DUNG lnd, NGUOI_DUNG nd Where nd.TenDangNhap = '{0}' and nd.idLoaiNguoiDung = lnd.id", strUsername);
            DataTable dt = _provider.ExecuteQueryDataTable(strSQL);
            if (dt != null)
            {
                return dt.Rows[0]["TenLoaiNguoiDung"].ToString();
            }
            else
                return "";
        }

        public DataTable GetMemberHistoryInfor(int memID)
        {
            string strT = "select hd.id as idHoaDon, NgayDatHang, NgayXacNhan, TongTien, NguoiXacNhan, NguoiDatHang, " +
                "idPTGH ,idPTTT, TenNguoiNhan, DiaChiNguoiNhan, SDTNguoiNhan, " +
                "ptgh.TenPhuongThuc as PhuongThucGiaoHang, pttt.TenPhuongThuc as PhuongThucThanhToan " +
                "from (HOA_DON hd join PT_GIAO_HANG ptgh on hd.idPTGH = ptgh.id) " +
                "join PT_THANH_TOAN pttt on hd.idPTTT = pttt.id " +
                "where NguoiDatHang = {0}";
            string strSQL = string.Format(strT,memID);
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public DataTable GetMemberFavouriteInfor(int memID)
        {
            string strT = "SELECT SP.ID as idSanPham, TenSanPham, MauSac, Gia, HA.SoLuongTon, Url "+
                "FROM SAN_PHAM SP JOIN HINH_ANH HA ON SP.id = HA.idSanPham "+
                "WHERE SP.ID IN (SELECT IDSANPHAM FROM SAN_PHAM_UA_THICH WHERE IDNGUOIDUNG = {0})";
            string strSQL = string.Format(strT, memID);
            return _provider.ExecuteQueryDataTable(strSQL);
        }

        public void removeFavPro(int mID, int pID)
        {
            string strSQL = string.Format("delete SAN_PHAM_UA_THICH where idSanPham={0} and idNguoiDung = {1}",
                pID, mID
                );

            _provider.ExecuteNonQuery(strSQL, true);
        }

        public string[] getInfo(int mID)
        {
            string strSQL = string.Format(
                "select * from NGUOI_DUNG where id = {0}",
                mID
                );
            DataTable dt = _provider.ExecuteQueryDataTable(strSQL);
            string[] rsl = new string[6];

            rsl[0] = dt.Rows[0]["Ten"].ToString();
            rsl[1] = dt.Rows[0]["DiaChi"].ToString();
            rsl[2] = dt.Rows[0]["DienThoai"].ToString();
            rsl[3] = dt.Rows[0]["NgaySinh"].ToString();
            rsl[4] = dt.Rows[0]["Email"].ToString();
            rsl[5] = dt.Rows[0]["NgheNghiep"].ToString();

            return rsl;
        }

        public void updateInfo(int mID,
                string sHoTen, string sDiaChi, string sSDT, string sNgaySinh, string sEmail, string sNgheNghiep)
        {
            string strSQL = string.Format(
                "update NGUOI_DUNG " +
                " set Ten = N'{0}', DiaChi=N'{1}', DienThoai='{2}', NgaySinh='{3}', Email='{4}', NgheNghiep=N'{5}' " +
                " where id={6} ",
                sHoTen, sDiaChi, sSDT, sNgaySinh, sEmail, sNgheNghiep, mID
                );
            _provider.ExecuteNonQuery(strSQL, true);
        }

        public int changePass(int memID, string hNewPass)
        {
            string strSQL = string.Format(
                "update NGUOI_DUNG " +
                "set MatKhau = '{0}' " +
                "where id={1}",
                hNewPass, memID
                );
            _provider.ExecuteNonQuery(strSQL, true);
            return 0;
        }
    }
}